﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using ANZ_AIM_DAL;
using System.Globalization;
using ANZ_AIM_BLL.Model;
using ANZ_AIM_BLL.Utils;
using ANZ_AIM_FRAMEWORK;


namespace ANZ_AIM_BLL
{
    public class UserBLL
    {
        private const string TABLE_NAME = "Users";
        private string ConnectionString;
        private Dictionary<string, string> ObjectPropDict;

        public UserBLL()
        {
            ConnectionString = DBHelper.GetMainMdbConnectionString();
            this.ObjectPropDict = BuildObjectPropertyMapping();
        }

        public User GetById(string userId)
        {
            try
            {

                var dict = MiscHelper.Clone(this.ObjectPropDict);
                OleDbParameter[] parameters;
                User user = new User { UserID = userId };
                var conditionDict = new Dictionary<string, string>
                    {
                        {"UserID", "UserID"}
                    };
                string command = CommandHelper.BuildSelectCommand(user, dict, conditionDict, out parameters);
                OleDbDataReader reader = OleDbHelper.ExecuteReader(this.ConnectionString, CommandType.Text, command, parameters);
                List<User> users = new List<User>();
                while (reader.Read())
                {
                    users.Add(PopulateObjectHelper.PopulateUserObject(reader));
                }
                reader.Close();
                return (users.Count > 0 ? users[0] : null);
            }
            catch (Exception ex) { throw ex; }
        }
        public User GetByUsername(string username)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                OleDbParameter[] parameters;
                User user = new User { UserName = username };
                var conditionDict = new Dictionary<string, string>
                    {
                        {"UserName", "UserName"}
                    };
                string command = CommandHelper.BuildSelectCommand(user, dict, conditionDict, out parameters);
                OleDbDataReader reader = OleDbHelper.ExecuteReader(this.ConnectionString, CommandType.Text, command, parameters);
                List<User> users = new List<User>();
                
                while (reader.Read())
                {
                    users.Add(PopulateObjectHelper.PopulateUserObject(reader));
                    
                }
                reader.Close();
                
                return (users.Count > 0 ? users[0] : null);
            }
            catch (Exception ex) { throw; }
        }
        public void DeleteUser(string userId)
        {
            try
            {
                User user = new User { UserID = userId };
                string command = string.Format("DELETE FROM [{0}] WHERE [UserID] =@UserID;", TABLE_NAME);
                OleDbParameter[] cmdParams = new OleDbParameter[1];
                cmdParams[0] = new OleDbParameter("@UserID", userId);
                int n = OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, cmdParams);
            }
            catch (Exception ex) { throw; }
        }
        public void Save(User user)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                OleDbParameter[] parameters;
                string command = string.Empty;
                if (string.IsNullOrEmpty(user.UserID))
                {
                    command = CommandHelper.BuildInsertCommand(user, dict, out parameters);
                }
                else
                {
                    dict.Remove("UserID");
                    var conditionDict = new Dictionary<string, string>
                    {
                        {"UserID", "UserID"}
                    };
                    command = CommandHelper.BuildUpdateCommand(user, dict, conditionDict, out parameters);
                }
                int n = OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
            }
            catch (Exception ex) { throw; }
        }
        public DataTable GetUsers()
        {
            try
            {
                string command = string.Format("SELECT UserID, UserName, FullName, Email, Role, IsActive, CreatedDate, CreatedBy, LastUpdatedDate, LastUpdatedBy FROM [{0}]", TABLE_NAME);
                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command);
                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }
        public DataTable Search(string userName, string role, string isActive)
        {
            try
            {
                string command = string.Format("SELECT UserID, UserName,FullName, Email, Role, IsActive, CreatedDate, CreatedBy, LastUpdatedDate, LastUpdatedBy FROM [{0}] WHERE (@UserName IS NULL OR [UserName] LIKE '%' + @UserName + '%') AND (@Role IS NULL OR Role LIKE '%' + @Role + '%') AND(@IsActive IS NULL OR IsActive=@IsActive);", TABLE_NAME);
                OleDbParameter[] parameters = new OleDbParameter[3];
                if (string.IsNullOrEmpty(userName))
                {
                    parameters[0] = new OleDbParameter("@UserName", DBNull.Value);
                }
                else
                {
                    parameters[0] = new OleDbParameter("@UserName", userName);
                }
                if (string.IsNullOrEmpty(role))
                {
                    parameters[1] = new OleDbParameter("@Role", DBNull.Value);
                }
                else
                {
                    parameters[1] = new OleDbParameter("@Role", role);
                }
                if (string.IsNullOrEmpty(isActive))
                {
                    parameters[2] = new OleDbParameter("@IsActive", DBNull.Value);
                }
                else
                {
                    if (isActive.Equals("1"))
                    {
                        parameters[2] = new OleDbParameter("@IsActive", true);
                    }
                    else
                    {
                        parameters[2] = new OleDbParameter("@IsActive", false);
                    }

                }

                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);

                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw; }
        }

        public Dictionary<string, string> BuildObjectPropertyMapping()
        {
            Dictionary<string, string> dictionary = new Dictionary<string, string>
            {
                {"UserID", "UserID"},
                {"UserName", "UserName"},
                {"Password", "Password"},
                {"Role", "Role"},
                {"Email", "Email"},
                {"FullName", "FullName"},
                {"IsActive", "IsActive"},
                {"CreatedBy", "CreatedBy"},
                {"CreatedDate", "CreatedDate"},
                {"LastUpdatedBy", "LastUpdatedBy"},
                {"LastUpdatedDate", "LastUpdatedDate"},
                {"MustChangePwd", "MustChangePwd"},
                {"LastChangedPwd", "LastChangedPwd"},
                {"IsLock", "IsLock"},
                {"PwdArray", "PwdArray"},
                {"LockDate", "LockDate"}
            };

            return dictionary;
        }
    }
}
